<?php

namespace App\Http\Controllers\Admin;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use DB;
use Storage;

/*第三方系统对接*/
class ThirdController extends Controller
{
    private $cacheMinutes = 7200; //缓存5天
    private function dbStat(){
        return [
            'type'=>[
                0=>'额定电压',
                1=>'分类',
                2=>'工器具类型',
                3=>'检测标准',
                4=>'检测项目',
                5=>'检测数据'
            ],
            'cache_key'=>[ //本页面涉及到的缓存key
                'third_system', //third表数据本系统参数
                'third_map', //third_map表数据
            ]
        ];
    }

    private function clearCache(){
        $cache_keys = $this->dbStat()['cache_key'];
        foreach($cache_keys as $key){
            cache()->forget($key);
        }
    }

    //通过参数获取第三方待检数据
    public function paramGetDj(){
        $third_id = request('third_id',0);
        $params = DB::table('a_third')->where('id',$third_id)->value('params');
        $params = json_decode($params,true);
        return view('admin.third.paramGetDj',compact('params'));
    }

    /*第三方平台项目列表*/
    public function testItem(){
        $third_id = request('third_id',0);
        if(request()->isMethod('post')){
            $this->clearCache();
            $thirdId = request('third_id',0);
            // dd($thirdId);
            $info = DB::table('a_third')->find($thirdId);
            $controller = $info->controller;
            $className = "App\\Http\\Controllers\\Api\\Third\\{$controller}";
            $apiCon = new $className($info);
            return $apiCon->getToolItems();
        }
        $data = DB::table('a_third_testitem')->where('third_id',$third_id)->get();
        return view('admin.third.testItem', compact('data'));
    }

    /* 第三方平台数据加到批次中 */
    public function dataAdd2Contract(){
        // dd(request()->all());
        $contract_id = request('contract_id',0);
        if (!$contract_id) {
            $org_names = request('org_names',[]);
            if (count($org_names) > 2) {
                array_shift($org_names);
            }
            // dd($org_names);
            $client_name = str_replace(' ','',str_replace('⬤', '', implode('',$org_names)));
            if(!$client_name){
                return '请选择送检单位！！！';
            }
            $clientId = DB::table('a_client')->where('name',$client_name)->value('id');
            if (!$clientId) {
                $clientId = DB::table('a_client')->insertGetId([
                    'name'=>$client_name,
                    'entrusted_company'=>$client_name,
                ]);
            }
            request()->offsetSet('client_id',$clientId);
            $contractCon = new ContractController();
            $contract_id = $contractCon->add(request());
            request()->offsetSet('contract_id',$contract_id);
            $contract_add = 1; //用于判断是否将该批次删除
            request()->offsetSet('contract_add',$contract_add);
            if(!is_numeric($contract_id)){
                DB::table('a_contract')->delete($contract_id);
                return $contract_id;
            }
        }
        $ids = request('ids',[]);
        /*检查数量 start*/
            //目前每个批次最多可以放999或9999
            $chars_count = DB::table('setting')->where('id',1)->value('chars_count');
            $max = (int)str_pad('',$chars_count,"9");
            $count = DB::table('a_contract_gaiyao')->where('contract_id',$contract_id)->sum('count');
            $left = $max - $count;
            if (count($ids) > $left) {
                if(isset($contract_add)){
                    DB::table('a_contract')->delete($contract_id);
                }
                return "该批次<span class='c-red'>最多</span>只能添加样品数量：<span class='c-red'>{$left}</span>";
            }
        /*检查数量 end*/
        $count = 0;
        $inData = [];
        foreach ($ids as $id) {
            $info = DB::table('a_third_data')->find($id);
            $std_id = $info->std_id;
            $tool_id = $info->tool_id;
            // dd($info);
            if ($info->upload == 0 || !$info->item_ids) {
                $info->item_ids = implode(',',DB::table('a_std_tool_item_rel')->where(['std_id'=>$std_id,'tool_id'=>$tool_id,'ishide'=>0])->orderBy('sort','asc')->pluck('testitem_id')->toArray());
            }
            $inData[$tool_id.'|'.$std_id.'|'.$info->vol_id.'|'.$info->class_id.'|'.$info->item_ids][] = $info;
        }
        // dd($inData);
        $gyCon = new \App\Http\Controllers\Admin\ContractGaiyaoController();
        foreach ($inData as $key => $item) {
            $res = $this->dataAddGy($gyCon,$key,$item);
            if ($res != 1) {
                if(isset($contract_add)){
                    DB::table('a_contract')->delete($contract_id);
                }
                return $res;
            }
        }
        return 1;
    }

    private function dataAddGy($gyCon,$paras,$infos){
        $info = current($infos);
        $contract_id = request('contract_id',0);
        $contract_add = request('contract_add',0);
        list($tool_id,$std_id,$vol_id,$class_id,$item_ids) = explode('|',$paras);
        if (!$item_ids) {
            $item_ids = DB::table('a_std_tool_item_rel')->where(['std_id'=>$std_id,'tool_id'=>$tool_id,'ishide'=>0])->pluck('testitem_id')->toArray();
        }
        if (!$item_ids) {
            return data_get($info,'third_tool_name').'对照的标准没项目！';
        }
        $afterId = DB::table('a_contract_gaiyao')->where('contract_id',$contract_id)->max('id');
        $gyAddParams = [
            'contract_id'=>$contract_id,
            'remark'=>'',
            'tool_id'=>$tool_id,
            'vol_id'=>$vol_id,
            'class_id'=>$class_id,
            'std_id'=>$std_id,
            'count'=>count($infos),
            'sub_count'=>1,
            'items'=>is_string($item_ids) ? explode(',',$item_ids):$item_ids,
            'after_id'=>$afterId ?: 0,
            'third_id'=>data_get($info,'third_id',0)
        ];
        foreach($gyAddParams as $col=>$val){
            request()->offsetSet($col,$val);
        }
        DB::beginTransaction();
        try{
            $gyId = $gyCon->add();
            $samples = DB::table('a_sample_info')->where('contract_gaiyao_id',$gyId)->get();
            // dd($samples);
            foreach ($samples as $k=>$sample) {
                $info = $infos[$k];
                DB::table('a_sample_info')->where('id',$sample->id)->update([
                    'guige_xinghao'=>$info->guige_xinghao,
                    'self_num'=>$info->self_num,
                    'manufacturer'=>$info->manufacturer,
                    'production_date'=>$info->production_date,
                    'sample_status'=>'外观无异常',
                    'third_id'=>$info->third_id2,
                ]);
                DB::table('a_third_data')->where('id',$info->id)->update(['bar_code'=>$sample->bar_code]);
            }
            DB::commit();
        }catch(\Exception $exception){
            DB::rollBack();
            if($contract_add){
                DB::table('a_contract')->delete($contract_id);
            }
            // dd($exception->getMessage().$exception->getLine());
            return '添加失败！请重试！';
        }
        return 1;
    }

    /* 设置分类 */
    public function setDataClass(){
        $ids = request('ids',[]);
        if (is_string($ids)) {
            $ids = explode(',',$ids);
        }
        $class = request('class','/');
        $class_id = request('class_id',0);
        $count = 0;
        foreach ($ids as $id) {
            $res = DB::table('a_third_data')->where('id',$id)->update(['class'=>$class,'class_id'=>$class_id]);
            $count += $res ? 1 : 0;
        }
        return $count;
    }
    /* 第三方数据展示/获取  待检样品 */
    public function data() {
        if (request()->isMethod('post')) {
            $type = request('type','');
            if ($type == 'check') {
                if (request('reset',0) == 1) {
                    DB::table('a_third_data')->whereNull('bar_code')->where('checked',1)->update(['checked'=>0]);
                }
                $thirdId2 = request('third_id2','');
                if ($thirdId2) {
                    //更新选中
                    DB::table('a_third_data')->whereNull('bar_code')->where('third_id2',$thirdId2)->update(['checked'=>1]);
                }
                $data = DB::table('a_third_data')->whereNull('bar_code')->where(['checked'=>1])->pluck('third_id2');
                return $data;
            }
            $third_id = request('third_id',0);
            return $this->getThirdDj($third_id); //获取第三方待检数据
        }
        // $thirds = $tools = $orgTree = $orgDj = $data =[];
        // return view('admin.third.data', compact('thirds','tools','orgTree','data','orgDj'));
        $thirds = DB::table('a_third')->get()->keyBy('id')->toArray();
        $data = DB::table('a_third_data as a')
            ->leftJoin('a_third as b','a.third_id','=','b.id')
            ->select('a.*','b.name as third_name')
            ->whereNull('a.bar_code')
            // ->orderBy('a.third_org_name','asc')
            ->orderBy('a.tool_name','asc')
            ->orderBy('a.voltage','asc')
            ->orderBy('a.class','asc')
            ->get()->toArray();
        $tools = array_count_values(array_column($data, 'tool_name')); //总结工器具都有哪些类型和数量、
        $orgTree = [];
        foreach($thirds as $third){
            if($third->blgSgsId){
                if(!isAdmin()){ //接口有问题的话会有时间浪费,所以这里增加admin限制
                    $orgTree[$third->id.'-'.$third->name] = $this->getOrgTree($third->id); 
                }
            }
        }
        // dd($orgTree);
        $orgDj = [];  //待检单位
        $data2 = collect($data)->sortByDesc('send_time');
        foreach($data2 as $item){
            $date = date('Y-m-d',strtotime($item->send_time));
            $thirdinfo = "{$item->third_id}][{$item->third_name}";
            $thirdorginfo = "{$item->third_org_id}][{$item->third_org_name}";
            $key = "{$thirdinfo}.{$date}.{$thirdorginfo}.{$item->tool_name}";
            array_set($orgDj,$key,array_get($orgDj,$key,0)+1);
        }
        return view('admin.third.data', compact('thirds','tools','orgTree','data','orgDj'));
    }

    private function downloadReport(){
        $id = request('id','');
        $sample = DB::table('a_third_data as a')
            ->leftJoin('a_sample_info as b','a.bar_code','=','b.bar_code')
            ->leftJoin('a_contract as c','b.contract_id','=','c.id')
            ->where('a.id',$id)
            ->select('a.third_id','a.third_id2','b.id as sample_info_id','b.self_num','c.batchNum')
            ->first();
        if(!$sample->sample_info_id){
            return '检测业务已删除！';
        }
        $self_num = $sample->self_num;
        $disk = Storage::disk('public');
        $report_style = DB::table('a_third')->where('id',$sample->third_id)->value('report_style') ?: 1;
        $ext = $report_style == 1 ?'.xls':'.pdf';
        $path = $disk->path('reports/'.$sample->batchNum.'/'.$sample->self_num.$ext);
        if(!file_exists($path)){
            request()->offsetSet('id',$sample->sample_info_id);
            if ($report_style == 2) {
                request()->offsetSet('toPdf',1);
            }
            request()->offsetSet('sample',1);
            $ExportReportController = new ExportReportController();
            return $ExportReportController->export(request());
        }
        return response()->download($path);
    }

    /* 第三方数据 全部数据
        参数：dj=>0已受理 1未受理 2全部
     */
    public function dataAll() {
        $action = request('action','');
        if ($action == 'download_report') {
            return $this->downloadReport();
        }
        $third_id = request('third_id',0);
        if(request()->expectsJson()){
            $where = [];
            if ($third_id) {
                // dd($third->params);
                $where[] = ['a.third_id','=',$third_id];
            }
            $dj = request('dj',1);
            switch($dj){
                case 1:
                    $where[] = ['a.bar_code','=',NULL]; //未受理
                break;
                case 0:
                    $where[] = ['a.bar_code','!=',NULL]; //已受理
                break;
                default:; //全部
            }
            $dataSql = DB::table('a_third_data as a')
                ->leftJoin('a_third as b','a.third_id','=','b.id')
                ->where($where);
            $formData = request('form',[]);
            foreach ($formData as $item) {
                $column = $item['name'];
                $value = $item['value'];
                if ($value) {
                    switch($column){
                        case 'batchNum':
                            $dataSql->where('a.bar_code','like',"{$value}%");
                            break;
                        case 'send_time1':
                            $start = str_replace('-','',$value);
                            $dataSql->where('a.send_time','>',$start);
                            break;
                        case 'send_time2':
                            $end = str_replace('-','',$value);
                            $dataSql->where('a.send_time','<',$end);
                            break;
                        case 'third_org_id':
                            $dataSql->where('a.third_org_id','like','%-'.$value.'-%');
                            break;
                        default: $dataSql->where('a.'.$column,'=',$value);
                            break;
                    }
                }
            }
            
            // dd($where);
            $count = $dataSql->count();
            $limit = request('length',100);
            $offset = request('page',0)*$limit;
            $sqldata = $dataSql->select('a.*','b.name as third_name')
                ->orderBy('third_org_name','asc')
                ->orderBy('tool_name','asc')
                ->orderBy('voltage','asc')
                ->orderBy('class','asc')
                ->offset($offset)->limit($limit)->get();
            // $sqldata = $dataSql->select('a.*','b.name as third_name')->get();
            $data = [];
            $data['data'] = $sqldata;
            $data['iTotalRecords'] = $data['iTotalDisplayRecords'] = $count;
            return response()->json($data);
        }
        $thirds = DB::table('a_third')->get()->keyBy('id')->toArray();
        $orgTree = [];
        foreach($thirds as $third){
            if($third->blgSgsId){
                if (!isAdmin()) {
                    $orgTree[$third->id.'-'.$third->name] = $this->getOrgTree($third->id);
                }
            }
        }
        return view('admin.third.dataAll', compact('thirds','orgTree'));
    }
    //删除第三方数据
    public function dataDel(){
        $ids = request('ids',[]);
        // dd($ids);
        $count = 0;
        foreach ($ids as $id) {
            $res = DB::table('a_third_data')->whereNull('bar_code')->delete($id);
            $count += $res ? 1 : 0;
        }
        return $count;
    }

    /*获取待检数据*/
    public function getThirdDj($thirdId=0){
        $thirdId = $thirdId ?: request('third_id',0);
        // dd($thirdId);
        $info = DB::table('a_third')->find($thirdId);
        if($info){
            $controller = $info->controller;
            $className = "App\\Http\\Controllers\\Api\\Third\\{$controller}";
            $apiCon = new $className($info);
            $thirdMap = $this->getThirdMap($thirdId);
            try{
                $res = $apiCon->getGqjBookTestInfo($thirdMap);
            }catch(\Exception $e){
                return $e->getMessage();
            }
            return $res;
        }
        return 0;
    }

    public function upload(){
        $bar_code = request('bar_code');
        $sample_id = DB::table('a_sample_info')->where('bar_code',$bar_code)->value('id');
        if (!$sample_id) {
            return '没找到样品';
        }
        $third_id = DB::table('a_third_data')->where('bar_code',$bar_code)->value('third_id');
        request()->offsetSet('sample_ids',[$sample_id]);
        request()->offsetSet('third_ids',[$third_id]);
        return $this->uploadRes();
    }

    public function uploadRes(){
        $contract_id = request('contract_id',0);
        if($contract_id){
            $sampleIdsC = DB::table('a_sample_info as a')
                ->leftJoin('a_contract_gaiyao as b','a.contract_gaiyao_id','=','b.id')
                ->leftJoin('a_third_data as c','a.bar_code','=','c.bar_code')
                ->where('b.contract_id',$contract_id)
                ->where('a.third_id','!=','')
                ->where('a.res','!=','0')
                ->where('c.upload','=',1)
                ->pluck('b.third_id','a.id')->toArray();

            request()->offsetSet('third_ids',array_values($sampleIdsC));
            request()->offsetSet('sample_ids',array_keys($sampleIdsC));
        }
        $thirdIds = request('third_ids',[]);
        $sampleIds = request('sample_ids',[]);
        if(!$sampleIds || !$thirdIds){
            return '没有可以上传的样品！';
        }
        $thirdSamples = [];  //总结每个批次下几个平台下的数据
        foreach ($thirdIds as $k => $thirdId) {
            $thirdSamples[$thirdId][] = data_get($sampleIds,$k,0); 
        }
        // dd($thirdSamples);
        foreach ($thirdSamples as $thirdId => $sampleIds2) {
            request('third_id',$thirdId);
            request()->offsetSet('sample_ids',$sampleIds2);
            // dd(request('sample_ids'));
            $info = DB::table('a_third')->find($thirdId);
            if (!$info) {
                return '参数错误！';
            }
            $controller = $info->controller;
            $className = "App\\Http\\Controllers\\Api\\Third\\{$controller}";
            $apiCon = new $className($info);
            $thirdMap = $this->getThirdMap($thirdId);
            $res = $apiCon->saveGqjBookTestInfo($thirdMap);
            if ($res != 1) {
                return $res;
            }
            foreach ($sampleIds as $sampleId) {
                DB::table('a_sample_info as a')
                    ->leftJoin('a_third_data as b','a.bar_code','=','b.bar_code')
                    ->where('a.id',$sampleId)
                    ->increment('b.up_times');
            }
        }
        return 1;
    }
    public function map() {
        $third_id = request('third_id',0);
        if (request()->isMethod('post')) {
            return $this->mapData();
        }
        $data = [];
        $data['info'] = DB::table('a_third')->find($third_id);
        $data['dbStat'] = $this->dbStat();
        //山东带电作业对照表信息
        $data['third'] = $this->getThirdMap($third_id);
        //本系统基础表信息
        $data['system'] = $this->getSysData($third_id);
        $toolStds1 = DB::table('a_std_tool_rel as a')
            ->leftJoin('a_standard as b','a.std_id','=','b.id')
            ->leftJoin('a_tool as c','a.tool_id','=','c.id')
            ->where('c.ishide',0)
            ->select('c.*','b.num as std_num')
            ->get();
        $toolStds = [];
        foreach ($toolStds1 as $item) {
            // code...
            $toolStds[$item->name][] = $item->std_num;
        }
        return view('admin.third.map',compact('data','toolStds'));
    }
    public function index() {
        $data = [];
        $data['list'] = DB::table('a_third')->get();
        return view('admin.third.index',compact('data'));
    }
    public function edit(){
        $id = request('id',0);
        $params = request()->only('name','base_url','controller','type','blgSgsId','canScan','org_id','report_style');
        $paras = request('params',[]);
        $params['params'] = [];
        if ($paras) {
            if ($id) {
                $paras = $paras[$id];
            }
            foreach($paras['name'] as $k => $name){
                if ($name) {
                    $params['params'][$name] = $paras['value'][$k].'&&'.$paras['must'][$k];
                }
            }
        }
        
        $params['params'] = json_encode($params['params']);
        if ($id) { //编辑
            try{
                DB::table('a_third')->where('id',$id)->update($params);
            }catch(\Exception $e){
                return $e->getMessage();
            }
            return 1;
        }else{ //添加
            try{
                if (DB::table('a_third')->where('name',$params['name'])->exists()) {
                    return '名称已存在！';
                }
                if (DB::table('a_third')->where('controller',$params['controller'])->exists()) {
                    return '控制器已存在！';
                }
                // dd($params);
                DB::table('a_third')->insert($params);
            }catch(\Exception $e){
                return $e->getMessage();
            }
            return 1;
        }
    }
    public function del(){
        $res = DB::table('a_third')->delete(request('id',0));
        return $res? 1 : 0;
    }

    public function getOrgTree($third_id = 0){
        $third = DB::table('a_third')->find(request('third_id',$third_id));
        if(!$third->blgSgsId){
            return [];
        }
        $controller = $third->controller;
        $className = "App\\Http\\Controllers\\Api\\Third\\{$controller}";
        $apiCon = new $className($third);
        $orgData = $apiCon->getOrgTree(request('orgId',''));
        if (!is_array($orgData)) {
            return [];
        }
        foreach($orgData as $k => &$item){
            $orgId = $item['value'];
            $item['tools'] = (int)DB::table('a_third_data')
                ->whereNull('bar_code')
                ->where('third_org_id','like',"%-{$orgId}-%")
                ->exists();
        }
        unset($item);
        return $orgData;
    }
    
    private function mapData(){
        $params = request()->only(['third_id','third_id2','type','sys_name','third_name','times','sys_id','upload','formula']);
        // dd($params);
        
        $uniqMap = [0,1,2,3];
        $thirdNames = $params['third_name'];
        if($thirdNames && in_array($params['type'],$uniqMap)){
            $thirdMaps = $this->getThirdMap($params['third_id']);
            $thirdMaps = array_get($thirdMaps,$params['type'],[]);
            $thirdMaps = array_get($thirdMaps,'third',[]);
            // dd($params,$thirdMaps,$thirdNames);
            $thirdNames = array_unique(explode(',',$thirdNames));
            // DD($thirdNames);
            foreach ($thirdNames as $thirdName) {
                if(isset($thirdMaps[$thirdName]) && $thirdMaps[$thirdName]['sys_name'] != $params['sys_name']) {
                    return $thirdName.'已有对照数据！';
                }
            }
        }
        $where = [
                'third_id'=>$params['third_id'], 
                'type'=>$params['type'], 
                'sys_name'=>$params['sys_name']
            ];
        DB::beginTransaction();
        try{
            if (!$thirdNames){
                $res = DB::table('a_third_map')->where($where)->delete();
            }else{
                if (DB::table('a_third_map')->where($where)->exists()) {
                    $res = DB::table('a_third_map')->where($where)->update($params);
                }else{
                    $res = DB::table('a_third_map')->insert($params);
                }
            }
            DB::commit();
        }catch(\Exception $e){
            DB::rollBack();
            return $e->getMessage();
        }
        
        $this->clearCache();
        return 1 ;
    }
    //获取某个对照网站的对照信息
    private function getThirdMap($third_id){
        $cacheKey = 'third_map';
        $thirdMaps = cache($cacheKey);
        if(!$thirdMaps){
            $thirdMapDatas = DB::table('a_third_map')->get();
            $thirdMaps = [];
            foreach ($thirdMapDatas as $item) {
                $item = (array)$item;
                $item['sys_name'] = htmlspecialchars_decode($item['sys_name']);
                $thirdMaps[$item['third_id']][$item['type']][$item['sys_name']] = $item;
                $thirdNames = explode(',',$item['third_name']);
                $thirdId2s = explode(',',$item['third_id2']);
                foreach($thirdNames as $k =>$thirdName){
                    $item['third_name'] = htmlspecialchars_decode($thirdName);
                    $item['third_id2'] = data_get($thirdId2s,$k,0);
                    if($item['type'] == 4 || $item['type'] == 5){ //因为检测项目和试验数据可能会出现一对多的情况，所以这里不缓存
                        $thirdMaps[$item['third_id']][$item['type']]['third'][$thirdName][$item['sys_name']] = $item;
                    }else{
                        $thirdMaps[$item['third_id']][$item['type']]['third'][$thirdName] = $item;
                    }
                }
            }
            // dd($thirdMaps);
            cache([$cacheKey=>$thirdMaps],$this->cacheMinutes); 
        }
        return data_get($thirdMaps,$third_id,[]);
    }

    //获取本系统的一些基础信息
    private function getSysData($third_id){
        $cacheKey = 'third_system';
        $system = cache($cacheKey);
        // $system = [];
        if (!$system) {
            $volClass = DB::table('a_voltage_class')->select('type','val')->get();
            $voltage = $class = [];
            foreach ($volClass as $item) {
                if ($item->type == 0) {
                    $voltage[] = $item->val;
                }else{
                    $class[] = $item->val;
                }
            }
            //$system下标 对应 $dbStat[type]下标
            $system[0] = array_unique($voltage);
            $system[1] = array_unique($class);
            $system[2] = DB::table('a_tool')->where('ishide',0)->pluck('name','id')->toArray();
            $system[3] = DB::table('a_standard')->pluck('num','id')->toArray();
            // $system[4] = DB::table('a_testitem')->pluck('name','id')->toArray();
            $system[4] = DB::table('a_std_tool_item_rel as a')
                    ->leftJoin('a_testitem as b','a.testitem_id','=','b.id')
                    ->leftJoin('a_tool as c','a.tool_id','=','c.id')
                    ->leftJoin('a_standard as d','a.std_id','=','d.id')
                    ->leftJoin('a_third_map as e',function ($query)use($third_id)
                    {
                        $query->on('e.sys_id','=','c.id')->where('e.type','=',2)->where('e.third_id','=',$third_id);  //工器具类型
                    })
                    ->leftJoin('a_third_map as f',function ($query)use($third_id)
                    {
                        $query->on('f.sys_id','=','d.id')->where('f.type','=',3)->where('f.third_id','=',$third_id); //标准
                    })
                    ->whereNotNull('e.third_name')
                    ->whereNotNull('f.third_name')
                    ->select(DB::raw("concat(c.name,' => ',d.num,' => ',b.name) as name"),'a.id')
                    // ->get();
                    ->pluck('name','id')->toArray();
            // dd($system[4]);
             $system[5] = array_filter(DB::table('a_testdata_key as a')
                    ->leftJoin('a_std_tool_item_rel as b','a.std_tool_item_rel_id','=','b.id')
                    ->leftJoin('a_testitem as c','b.testitem_id','=','c.id')
                    ->leftJoin('a_standard as d','b.std_id','=','d.id')
                    ->leftJoin('a_tool as e','b.tool_id','=','e.id')
                    ->leftJoin('a_third_map as g',function ($query)use($third_id)
                    {
                        $query->on('g.sys_id','=','e.id')->where('g.type','=',2)->where('g.third_id','=',$third_id);  //工器具类型
                    })
                    ->leftJoin('a_third_map as f',function ($query)use($third_id)
                    {
                        $query->on('f.sys_id','=','d.id')->where('f.type','=',3)->where('f.third_id','=',$third_id); //标准
                    })
                    ->leftJoin('a_third_map as h',function ($query)use($third_id)
                    {
                        $query->on('h.sys_id','=','b.id')->where('h.type','=',4)->where('h.third_id','=',$third_id); //项目
                    })
                    ->whereNotNull('g.third_name')
                    ->whereNotNull('f.third_name')
                    ->whereNotNull('h.third_name')
                    ->select(DB::raw("concat(e.name,' => ',d.num,' => ',c.name,' => ',a.data_key,'（',a.data_unit,'）') as name"),'a.id')
                    ->orderBy('name','asc')
                    ->pluck('name','id')->toArray());
            // dd($system[5] );
            cache([$cacheKey=>$system],$this->cacheMinutes); 
        }
        return $system;
    }
}
